﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data;

namespace ath2011_sql_WPF
{
    /// <summary>
    /// Interaction logic for FormTest.xaml
    /// </summary>
    public partial class FormTest : Window
    {
        string conStr;

        public FormTest(string conStr1)
        {
        
            InitializeComponent();
            conStr = conStr1;
            txtConnection.Text = conStr;
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            string str = "SELECT * FROM tblContacts";
            

            SqlCommand myCommand = new SqlCommand(str, con);
            SqlDataReader myReader = myCommand.ExecuteReader();
            
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        for (int i = 0; i < myReader.FieldCount; i++)
                        {
                            textBox1.Text += myReader[i].ToString() + " ";
                        }
                        textBox1.Text += "\r\n";
                    }
                }

             con.Close();

        }

        private void button6_Click(object sender, RoutedEventArgs e)
        {
            SqlConnection myConn = new SqlConnection(conStr);
            string str = "SELECt * FROM tblContacts";
            myConn.Open();

            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(str,myConn);

            DataSet myDataSet = new DataSet();

            
            mySqlDataAdapter.Fill(myDataSet, "tblContacts");


            DataTable myDataTable = myDataSet.Tables["tblContacts"];

            DataRow myDataRow = myDataTable.NewRow();
            myDataRow["FIRSTNAME"] = "Jan";
            myDataRow["LASTNAME"] = "Man";


            myDataTable.Rows.Add(myDataRow);
            myConn.Close();

            SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
            mySqlDataAdapter.Update(myDataSet, "tblContacts");
        }

        private void button8_Click(object sender, RoutedEventArgs e)
        {
            
            SqlConnection myConn = new SqlConnection(conStr);
            string str = "SELECt * FROM tblContacts";
            myConn.Open();

            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(str,myConn);
            DataSet myDataSet = new DataSet();
            mySqlDataAdapter.Fill(myDataSet, "tblContacts");


            DataTable myDataTable = myDataSet.Tables["tblContacts"];

            DataRow[] myDataRow = myDataTable.Select("ID =1");
             for (int i = 0; i < myDataRow.GetLength(0); i++)
             {
              myDataRow[i].BeginEdit();
 
              myDataRow[i]["FIRSTNAME"] = "Pan";

	          myDataRow[i].EndEdit();
             }

            
            myConn.Close();

            SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
            mySqlDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand();
            mySqlDataAdapter.Update(myDataSet, "tblContacts");
        }

        private void button2_Click(object sender, RoutedEventArgs e)
        {

        }

        private void button3_Click(object sender, RoutedEventArgs e)
        {
            SqlConnection myConn = new SqlConnection(conStr);
            string str = "SELECt * FROM tblContacts";
            myConn.Open();

            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(str, myConn);
            DataSet myDataSet = new DataSet();
            mySqlDataAdapter.Fill(myDataSet, "tblContacts");


            DataTable myDataTable = myDataSet.Tables["tblContacts"];

            DataRow[] myDataRow = myDataTable.Select("ID =1");
            for (int i = 0; i < myDataRow.GetLength(0); i++)
            {
                myDataRow[i].BeginEdit();

                myDataRow[i].Delete();

                myDataRow[i].EndEdit();
            }


            myConn.Close();

            SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
            mySqlDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand();
            mySqlDataAdapter.Update(myDataSet, "tblContacts");
        }

        private void button7_Click(object sender, RoutedEventArgs e)
        {
            SqlConnection con1;
            con1 = new SqlConnection(conStr);
            con1.Open();

            string s1 = @"CREATE PROCEDURE Procedure3
                          @LASTNAME varchar(100)
                          AS
                          DELETE tblContacts WHERE LASTNAME=@LASTNAME";

            SqlCommand cmd1 = new SqlCommand(s1, con1);
            try
            {
                cmd1.ExecuteNonQuery();
            }
            catch { }

            s1 = "Procedure3";
            cmd1 = new SqlCommand(s1, con1);
            cmd1.CommandType = CommandType.StoredProcedure;

            cmd1.Parameters.Add(new SqlParameter("@LASTNAME", SqlDbType.VarChar));
            cmd1.Parameters["@LASTNAME"].Value = "PI 314";

            cmd1.ExecuteNonQuery();
        }
    }
}
